import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * Class describe data base entity Topic
 * allow select, insert and update entity
 * @author anton
 *
 */
public class EW_Topic extends DataBaseAccess {
	/** table name in data base */
	static private String tableName = "Topic";
	/** true if entity is't in db */
	static private boolean isNew = true;
	/** topic name in db or new name */
	private String topicName = "";
	/** language name for current topic */
	private String topicLanguage = "";
	
	/**
	 * Constructor, create element of Topic <br>
	 * if name exists in DB for given language, then
	 * variable {@link isNew} while false
	 * @param name - name of topic
	 * @param language - language for topic
	 */
	public EW_Topic(String name, String language){
		super(tableName);
		topicName = name;
		topicLanguage = language;
		if (isNew) {
			if (!isTopicExists())
				insertCurrentRecord();
		}
	}
	
	/**
	 * Get current name of topic
	 * @return	String - name of current topic
	 */
	public String getTopicName(){
		return topicName;
	}
	
	/**
	 * Get language name for current topic
	 * @return	String - name of language for current topic 
	 */
	public String getTopicLanguage(){
		return topicLanguage;
	}
	
	/**
	 * check current name and language for existing in db
	 * @return - true if record binded in DB, false for other side
	 */
	private boolean isTopicExists(){
		String query = "Select * from " + tableName + " where name = '" + topicName.toLowerCase() + "' and stadyLanguage = '" + topicLanguage.toLowerCase() + "'";
		try{
			EWDataBase db = EWDataBase.getInstance();
			ResultSet rs = db.conn.prepareStatement(query).executeQuery();
			while (rs.next()) {
				rs.close();
				return true;
			}
			rs.close();
			return false;
		}
		catch (Exception exept) {
			exept.printStackTrace();
			return false;
		}
		
	}
	
	/**
	 * Return topic list by word id, or words ids.
	 * @param operation - sql operation e.t. (=, <>, in ...) 
	 * @param value - word id, value for operation
	 * @return array list of topics
	 */
	public static ArrayList<EW_Topic> getTopicListByWord(String operation, String value){
		try{
			HashMap<String, String> keys = new HashMap<String, String>();
			keys.put("operation", operation);
			keys.put("value", value);
			ResultSet rs = getMyTopicListByWord(keys);
			return getTopicListByRS(rs);
		}
		catch (SQLException exept) {
			exept.printStackTrace();
			isNew = true;
			return null;
		}
	}
	
	/**
	 * Return topic list by topic name and studied Language
	 * @param operation - sql operation e.t. (=, <>, in, like, starting with, ...) 
	 * @param value - value for operation
	 * @param stadyLanguage - varchar 20 key for language
	 *  </ul> 
	 * @return array list of topics
	 */
	public static ArrayList<EW_Topic> getTopicList(String operation, String value, String stadyLanguage){
		try{
			HashMap<String, String> keys = new HashMap<String, String>();
			keys.put("operation", operation);
			keys.put("value", value);
			keys.put("stadyLanguage", stadyLanguage);
			ResultSet rs = getMyListByDetail(keys);
			return getTopicListByRS(rs);
		}
		catch (SQLException exept) {
			exept.printStackTrace();
			isNew = true;
			return null;
		}
	}

	/**
	 * Fill array list by Result set
	 * @param rs - Result for executed SQL for select from Topic 
	 * @return - Array list of EW_Topic
	 * @throws SQLException - if Result set is Null or not from Topic table
	 */
	private static ArrayList<EW_Topic> getTopicListByRS(ResultSet rs)
			throws SQLException {
		ArrayList<EW_Topic> list = new ArrayList<EW_Topic>();
		isNew = false;
		while (rs.next()) {
			EW_Topic topic = new EW_Topic(rs.getString("name"), rs.getString("stadyLanguage"));
			list.add(topic);
		}
		isNew = true;
		rs.close();
		return list;
	}

	/**
	 * Prepare sql statement and execute query select all topics by one word
	 * @param keys - parameters for select
	 * @return result set from Topic table
	 */
	private static final ResultSet getMyTopicListByWord(HashMap<String, String> keys) {
		String query = "Select " + tableName + ".* from " + tableName + " join WordInTopic on " +
				"WordInTopic.topicName = " + tableName + ".name and WordInTopic.wordID ";
		query += keys.get("operation");
		query += keys.get("value");
		try {
			EWDataBase db = EWDataBase.getInstance();
			return db.conn.prepareStatement(query).executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * Prepare sql statement and select data from Topic table
	 * @param keys - parameters for select
	 * @return result set from Topic table
	 */
	private static final ResultSet getMyListByDetail(HashMap<String, String> keys) {
		String query = "Select * from " + tableName + " where name ";
		query += keys.get("operation") + " ";
		query += keys.get("value") + " ";
		query += "and stadyLanguage = '" + keys.get("stadyLanguage") + "'";
		try {
			EWDataBase db = EWDataBase.getInstance();
			return db.conn.prepareStatement(query).executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * Delete current record from DB
	 * @return - integer result of executing
	 */
	public int deleteCurrentRecord(){
		ArrayList<String> keys = new ArrayList<String>();
		keys.add("name = '" + topicName + "'");
		keys.add("stadyLanguage = '" + topicLanguage.toLowerCase() + "'");
		return super.deleteOneRecord(keys);		
	}
	
	/**
	 * Delete all topics where wordId = word.id
	 * @param word - word for deleting
	 * @return - result of executing
	 */
	public int deleteWordsTopics(EW_Vocabulary word){
		String insertSQL = "DELETE FROM WordInTopic WHERE wordID = " + Integer.toString(word.getId());
		try {
			java.sql.PreparedStatement ps = db.conn.prepareStatement(insertSQL);
			ps.execute();
			return ps.getUpdateCount();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	/**
	 * Insert prepared object in DB
	 * @return - integer result of executing
	 */
	private int insertCurrentRecord(){
		HashMap<String, String> insertValues = new HashMap<String, String>();
		insertValues.put("name".toUpperCase(), topicName.toLowerCase());
		insertValues.put("stadyLanguage".toUpperCase(), topicLanguage.toLowerCase());
		return super.insertOneRecord(insertValues);
	}
	
	/**
	 * overload procedure for compare objects
	 */
	public boolean equals(Object obj){
	    if(obj == null)
		    return false;

	    if(obj == this)
	    	return true;
	     
	    if(!(getClass() == obj.getClass()))
	    	return false;
	    else {
	      EW_Topic tmp = (EW_Topic)obj;
	      if(tmp.getTopicName().equals(this.getTopicName()) &&
	    	 tmp.getTopicLanguage().equals(this.getTopicLanguage()))
	    	  return true;
	      else
	    	  return false;
	    }
	  }
	
	/**
	 * join current topic and one word
	 * @param word - word for adding to topic
	 */
	public void joinWordAndTopic(EW_Vocabulary word){
		String insertSQL = "INSERT INTO WordInTopic VALUES ('" +
		topicName.toLowerCase() + "', " + Integer.toString(word.getId()) + ")";
		
		try {
			db.conn.prepareStatement(insertSQL).execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	@Deprecated
	protected final ResultSet getListByDetail(HashMap<?, ?> keys) {
		return null;
	}

}
